﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace Member.Sys.DAL
{
    public class DAL_Users
    {
        public DAL_Users() { }


        /// <summary>
        /// 登陆验证
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public bool Exists(string username, string password)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from [Users]");
            strSql.Append(" where Name=@username and Passwd=@password ");
            SqlParameter[] parameters = {
                    new SqlParameter("@username", SqlDbType.NChar,50),
                    new SqlParameter("@password", SqlDbType.NChar,50)};
            parameters[0].Value = username;
            parameters[1].Value = password;
            return Member.DB.DbHelpers.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 验证用户名是否存在
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool Exists(string name)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from [Users]");
            strSql.Append(" where Name=@name ");
            SqlParameter[] parameters = {
                    new SqlParameter("@name", SqlDbType.VarChar,50)};
            parameters[0].Value = name;

            return Member.DB.DbHelpers.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int bh)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Users");
            strSql.Append(" where ID=@bh ");
            SqlParameter[] parameters = {
					new SqlParameter("@bh", SqlDbType.Int,4)};
            parameters[0].Value = bh;

            return Member.DB.DbHelpers.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 添加一条数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(Member.Sys.Model.Model_Users model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Users(");
            strSql.Append("Name,Passwd,EMail,Phone,Type,Product,RegTime,regIP,LoginTime,LoginIP,Session,PicTure,StartTime,EndTime,Status)");
            strSql.Append(" values (");
            strSql.Append("@Name,@Passwd,@EMail,@Phone,@Type,@Product,@RegTime,@regIP,@LoginTime,@LoginIP,@Session,@PicTure,@StartTime,@EndTime,@Status)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@Name", SqlDbType.VarChar),
					new SqlParameter("@Passwd", SqlDbType.VarChar),
					new SqlParameter("@EMail", SqlDbType.VarChar),
					new SqlParameter("@Phone", SqlDbType.VarChar),
					new SqlParameter("@Type", SqlDbType.Int),
					new SqlParameter("@Product", SqlDbType.VarChar),
					new SqlParameter("@RegTime", SqlDbType.DateTime),
					new SqlParameter("@regIP", SqlDbType.VarChar),
					new SqlParameter("@LoginTime", SqlDbType.DateTime),
					new SqlParameter("@LoginIP", SqlDbType.VarChar),
					new SqlParameter("@Session", SqlDbType.VarChar),
					new SqlParameter("@PicTure", SqlDbType.VarChar),
                    new SqlParameter("@StartTime", SqlDbType.DateTime),
                    new SqlParameter("@EndTime", SqlDbType.DateTime),
                    new SqlParameter("@Status",SqlDbType.Int),                    };
            parameters[0].Value = model.Name;
            parameters[1].Value = model.Passwd;
            parameters[2].Value = model.Email;
            parameters[3].Value = model.Phone;
            parameters[4].Value = model.Type;
            parameters[5].Value = model.Product;
            parameters[6].Value = model.RegTime;
            parameters[7].Value = model.RegIP;
            parameters[8].Value = model.LoginTime;
            parameters[9].Value = model.LoginIp;
            parameters[10].Value = model.Session;
            parameters[11].Value = model.PicTure;
            parameters[12].Value = model.StartTime;
            parameters[13].Value = model.EndTiem;
            parameters[14].Value = model.Status;
            object obj = Member.DB.DbHelpers.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="model"></param>
        public void Update(Member.Sys.Model.Model_Users model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Users set ");
            strSql.Append("Name=@Name,");
            strSql.Append("Passwd=@UgroupID,");
            strSql.Append("Email=@Email,");
            strSql.Append("Phone=@Phone,");
            strSql.Append("Type=@Type,");
            strSql.Append("Product=@Product,");
            strSql.Append("RegTime=@RegTime,");
            strSql.Append("RegIP=@RegIP,");
            strSql.Append("LoginTime=@LoginTime,");
            strSql.Append("LoginIp=@LoginIp,");
            strSql.Append("Session=@Session,");
            strSql.Append("PicTure=@PicTure,");
            strSql.Append("StartTime=@StartTime,");
            strSql.Append("EndTime=@EndTime");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@Name", SqlDbType.VarChar),
					new SqlParameter("@Passwd", SqlDbType.VarChar),
					new SqlParameter("@EMail", SqlDbType.VarChar),
					new SqlParameter("@Phone", SqlDbType.VarChar),
					new SqlParameter("@Type", SqlDbType.Int),
					new SqlParameter("@Product", SqlDbType.VarChar),
					new SqlParameter("@RegTime", SqlDbType.DateTime),
					new SqlParameter("@regIP", SqlDbType.VarChar),
					new SqlParameter("@LoginTime", SqlDbType.DateTime),
					new SqlParameter("@LoginIP", SqlDbType.VarChar),
					new SqlParameter("@Session", SqlDbType.VarChar),
					new SqlParameter("@PicTure", SqlDbType.VarChar),
                    new SqlParameter("@StartTime", SqlDbType.DateTime),
                    new SqlParameter("@EndTime", SqlDbType.DateTime)};
            parameters[0].Value = model.Name;
            parameters[1].Value = model.Passwd;
            parameters[2].Value = model.Email;
            parameters[3].Value = model.Phone;
            parameters[4].Value = model.Type;
            parameters[5].Value = model.Product;
            parameters[6].Value = model.RegTime;
            parameters[7].Value = model.RegIP;
            parameters[8].Value = model.LoginTime;
            parameters[9].Value = model.LoginIp;
            parameters[10].Value = model.Session;
            parameters[11].Value = model.PicTure;
            parameters[12].Value = model.ID;
            parameters[12].Value = model.StartTime;
            parameters[13].Value = model.EndTiem;
            Member.DB.DbHelpers.NonQuery(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="ID"></param>
        public void Delete(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Users");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int)};
            parameters[0].Value = ID;
            Member.DB.DbHelpers.NonQuery(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除数据   
        /// </summary>
        /// <param name="strWhere">如:(ID=1)or (Name=admin)</param>
        public void Delete(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete  ");
            strSql.Append(" FROM [Users] ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            Member.DB.DbHelpers.NonQuery(strSql.ToString());
        }

        /// <summary>
        /// 更改用户密码  
        /// </summary>
        /// <param name="id">ID</param>
        /// <param name="password">密码</param>
        public void Update_password(int id, string password)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" update  ");
            strSql.Append(" [Users] SET Passwd = '" + password + "' where  ID='" + id + "' ");
            Member.DB.DbHelpers.NonQuery(strSql.ToString());
        }

        /// <summary>
        /// 更新用户密码
        /// </summary>
        /// <param name="user">用户名</param>
        /// <param name="password">密码</param>
        public void Update_password(string user, string password)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" update  ");
            strSql.Append(" [Users] SET password = '" + password + "' where  Name='" + user + "' ");
            Member.DB.DbHelpers.NonQuery(strSql.ToString());
        }
        /// <summary>
        /// 更新状态
        /// </summary>
        /// <param name="id"></param>
        /// <param name="Status"></param>
        public void UpdateStatus(int id, int Status)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" update  ");
            strSql.Append(" [Users] SET Status = '" + Status + "' where  ID='" + id + "' ");
            Member.DB.DbHelpers.NonQuery(strSql.ToString());
        }

        /// <summary>
        /// 更新状态
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="Status"></param>
        public void UpdateStatus(string Name, int Status)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" update  ");
            strSql.Append(" [Users] SET Status = '" + Status + "' where  Name='" + Name + "' ");
            Member.DB.DbHelpers.NonQuery(strSql.ToString());
        }

        /// <summary>
        /// 更新授权时间
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="EndTime"></param>
        public void UpdateEndTime(int ID, DateTime EndTime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" update  ");
            strSql.Append(" [Users] SET Status = '" + EndTime + "' where  ID='" + ID + "' ");
            Member.DB.DbHelpers.NonQuery(strSql.ToString());
        }

        /// <summary>
        /// 更新授权时间
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="EndTime"></param>
        public void UpdateEndTime(string Name, DateTime EndTime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" update  ");
            strSql.Append(" [Users] SET Status = '" + EndTime + "' where  Name='" + Name + "' ");
            Member.DB.DbHelpers.NonQuery(strSql.ToString());
        }

        /// <summary>
        /// 根据ID得到一个实体
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public Member.Sys.Model.Model_Users GetModel(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ID,Name,Passwd,EMail,Phone,Type,Product,RegTime,regIP,LoginTime,LoginIP,Session,PicTure from Users");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int)};
            parameters[0].Value = ID;

            Member.Sys.Model.Model_Users model = new Member.Sys.Model.Model_Users();
            DataSet ds = Member.DB.DbHelpers.GetDataSet(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }

                if (ds.Tables[0].Rows[0]["Name"].ToString().Trim() != "")
                {
                    model.Name = ds.Tables[0].Rows[0]["Name"].ToString();
                }

                if (ds.Tables[0].Rows[0]["Passwd"].ToString().Trim() != "")
                {
                    model.Passwd = ds.Tables[0].Rows[0]["Passwd"].ToString();
                }

                if (ds.Tables[0].Rows[0]["EMail"].ToString().Trim() != "")
                {
                    model.Email = ds.Tables[0].Rows[0]["EMail"].ToString();
                }

                if (ds.Tables[0].Rows[0]["Phone"].ToString().Trim() != "")
                {
                    model.Phone = ds.Tables[0].Rows[0]["Phone"].ToString();
                }

                if (ds.Tables[0].Rows[0]["Type"].ToString().Trim() != "")
                {
                    model.Type = int.Parse(ds.Tables[0].Rows[0]["Type"].ToString());
                }

                if (ds.Tables[0].Rows[0]["Product"].ToString().Trim() != "")
                {
                    model.Product = ds.Tables[0].Rows[0]["Product"].ToString();
                }

                if (ds.Tables[0].Rows[0]["RegTime"].ToString() != "")
                {
                    model.RegTime = DateTime.Parse(ds.Tables[0].Rows[0]["RegTime"].ToString());
                }

                if (ds.Tables[0].Rows[0]["RegIP"].ToString() != "")
                {
                    model.RegIP = ds.Tables[0].Rows[0]["RegIP"].ToString();
                }

                if (ds.Tables[0].Rows[0]["LoginTime"].ToString() != "")
                {
                    model.LoginTime = DateTime.Parse(ds.Tables[0].Rows[0]["LoginTime"].ToString());
                }

                if (ds.Tables[0].Rows[0]["LoginIP"].ToString().Trim() != "")
                {
                    model.LoginIp = ds.Tables[0].Rows[0]["LoginIP"].ToString();
                }

                if (ds.Tables[0].Rows[0]["Session"].ToString().Trim() != "")
                {
                    model.Session = ds.Tables[0].Rows[0]["Session"].ToString();
                }

                if (ds.Tables[0].Rows[0]["PicTure"].ToString().Trim() != "")
                {
                    model.PicTure = ds.Tables[0].Rows[0]["PicTure"].ToString();
                }

                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 根据用户名得到一个实体
        /// </summary>
        /// <param name="Name"></param>
        /// <returns></returns>
        public Member.Sys.Model.Model_Users GetModel(string Name)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ID,Name,Passwd,EMail,Phone,Type,Product,RegTime,regIP,LoginTime,LoginIP,Session,PicTure,StartTime,EndTiem,Status from Users");
            strSql.Append(" where Name=@Name ");
            SqlParameter[] parameters = {
					new SqlParameter("@Name", SqlDbType.NVarChar)};
            parameters[0].Value = Name;

            Member.Sys.Model.Model_Users model = new Member.Sys.Model.Model_Users();
            DataSet ds = Member.DB.DbHelpers.GetDataSet(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }

                if (ds.Tables[0].Rows[0]["Name"].ToString().Trim() != "")
                {
                    model.Name = ds.Tables[0].Rows[0]["Name"].ToString();
                }

                if (ds.Tables[0].Rows[0]["Passwd"].ToString().Trim() != "")
                {
                    model.Passwd = ds.Tables[0].Rows[0]["Passwd"].ToString();
                }

                if (ds.Tables[0].Rows[0]["EMail"].ToString().Trim() != "")
                {
                    model.Email = ds.Tables[0].Rows[0]["EMail"].ToString();
                }

                if (ds.Tables[0].Rows[0]["Phone"].ToString().Trim() != "")
                {
                    model.Phone = ds.Tables[0].Rows[0]["Phone"].ToString();
                }

                if (ds.Tables[0].Rows[0]["Type"].ToString().Trim() != "")
                {
                    model.Type = int.Parse(ds.Tables[0].Rows[0]["Type"].ToString());
                }

                if (ds.Tables[0].Rows[0]["Product"].ToString().Trim() != "")
                {
                    model.Product = ds.Tables[0].Rows[0]["Product"].ToString();
                }

                if (ds.Tables[0].Rows[0]["RegTime"].ToString() != "")
                {
                    model.RegTime = DateTime.Parse(ds.Tables[0].Rows[0]["RegTime"].ToString());
                }

                if (ds.Tables[0].Rows[0]["RegIP"].ToString() != "")
                {
                    model.RegIP = ds.Tables[0].Rows[0]["RegIP"].ToString();
                }

                if (ds.Tables[0].Rows[0]["LoginTime"].ToString() != "")
                {
                    model.LoginTime = DateTime.Parse(ds.Tables[0].Rows[0]["LoginTime"].ToString());
                }

                if (ds.Tables[0].Rows[0]["LoginIP"].ToString().Trim() != "")
                {
                    model.LoginIp = ds.Tables[0].Rows[0]["LoginIP"].ToString();
                }

                if (ds.Tables[0].Rows[0]["Session"].ToString().Trim() != "")
                {
                    model.Session = ds.Tables[0].Rows[0]["Session"].ToString();
                }

                if (ds.Tables[0].Rows[0]["PicTure"].ToString().Trim() != "")
                {
                    model.PicTure = ds.Tables[0].Rows[0]["PicTure"].ToString();
                }

                if (ds.Tables[0].Rows[0]["StartTime"].ToString() != "")
                {
                    model.StartTime = DateTime.Parse(ds.Tables[0].Rows[0]["StartTime"].ToString());
                }

                if (ds.Tables[0].Rows[0]["EndTiem"].ToString() != "")
                {
                    model.EndTiem = DateTime.Parse(ds.Tables[0].Rows[0]["EndTiem"].ToString());
                }
                
                if (ds.Tables[0].Rows[0]["Status"].ToString() != "")
                {
                    model.Status = int.Parse(ds.Tables[0].Rows[0]["Status"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }



    }
}
